/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package TimeRecording.TimeClockServer.database;

import TimeRecording.TimeClockServer.dtos.DatabaseTransaction;
import TimeRecording.TimeClockServer.dtos.DatabaseClockedPeriod;
import TimeRecording.TimeClockServer.dtos.BadgeDetails;
import TimeRecording.TimeClockServer.enums.ClockedInOrOut;
import TimeRecording.enums.EventType;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.Formatter;
import java.util.GregorianCalendar;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author dpatchett
 */
public class ClockDatabase {

    private final static String TEST_CONNECTION =
            "SELECT * " +
            "FROM control";

    private final static String GET_CLOCKED_PERIODS_FOR_BADGE =
            "SELECT " +
                "CP.badge_id, " +
                "CP.employee_id, " +
                "Concat(E.forename, ' ', E.surname) AS employee_name, " +
                "CP.clocked_in, " +
                "CP.clocked_out, " +
                "CP.synthetic_in, " +
                "CP.synthetic_out " +
            "FROM clocked_period CP " +
            "LEFT JOIN employee E ON " +
                "E.employee_id = CP.employee_id " +
            "WHERE CP.badge_id = ? AND " +
                "CP.clocked_in >= ? AND " +
                "CP.clocked_in < ? " +
            "ORDER BY CP.clocked_in";

    private final static String CLOCKED_PERIOD_HOUSEKEEPING =
            "UPDATE clocked_period SET " +
                "clocked_out = AddDate(clocked_in, INTERVAL 1 HOUR), " +
                "synthetic_out = 1 " +
            "WHERE clocked_out IS NULL";

    private final static String GET_CLOCKED_PERIODS_FOR_HOUSEKEEPING =
            "SELECT " +
                "CP.badge_id, " +
                "CP.employee_id, " +
                "Concat(E.forename, ' ', E.surname) AS employee_name, " +
                "CP.clocked_in, " +
                "CP.clocked_out, " +
                "CP.synthetic_in, " +
                "CP.synthetic_out " +
            "FROM clocked_period CP " +
            "LEFT JOIN employee E ON " +
                "E.employee_id = CP.employee_id " +
            "WHERE CP.clocked_out IS NULL";

    private final static String GET_EMPLOYEE_FOR_BADGE =
            "SELECT B.badge_id, " +
                "E.employee_id, " +
                "Concat(E.forename, ' ', E.surname) AS employee_name " +
            "FROM badge B " +
            "LEFT JOIN employee E ON E.employee_id = B.employee_id " +
            "WHERE B.badge_id = ?";

    private final static String GET_EMPLOYEES_AND_BADGES =
            "SELECT B.badge_id AS badge_id, " +
                "E.employee_id, " +
                "Concat(E.forename, ' ', E.surname) AS employee_name " +
            "FROM badge B " +
            "LEFT JOIN employee E ON E.employee_id = B.employee_id";

    private final static String INSERT_PERIOD =
            "INSERT INTO clocked_period (" +
                "badge_id, " +
                "employee_id, " +
                "clocked_in, "+
                "clocked_out, " +
                "synthetic_in, " +
                "synthetic_out) " +
            "VALUES (?, ?, ?, ?, ?, ?)";

    private final static String UPDATE_PERIOD =
            "UPDATE clocked_period SET " +
                "employee_id = ?, " +
                "clocked_out = ?, " +
                "synthetic_in = ?, " +
                "synthetic_out = ? " +
            "WHERE badge_id = ? AND " +
                "clocked_in = ?";

    private final static String INSERT_EVENT =
            "INSERT INTO event (" +
                "employee_id, " +
                "event_time, " +
                "acknowledged_time, " +
                "event_type) " +
            "VALUES (?, ?, ?, ?)";

    private final static String RECORD_CLOCK_TIME =
            "UPDATE control SET " +
                "clock_time = ?, " +
                "system_time = ?, " +
                "database_time = now()";

    private final static String GET_CONTROL_COLUMN =
            "SELECT %1$s FROM control";

    private final static String SET_CONTROL_COLUMN =
            "UPDATE control SET %1$s = %2$d";

    private String url = "jdbc:mysql://Daisy-Chain-Server.local:3306/time_recording_live";
    private String username = "DaisyChain";
    private String password = "DCP12345";
    private String driver = "com.mysql.jdbc.Driver";
    private boolean disconnected = true;

    private Connection conn;

    /**
     * Simply sets the various variables needed to connect to the database.  If
     * any parameter is supplied as null then a default value is  used.
     *
     * @param url database URL
     * @param username username of login credentials
     * @param password password of login credentials
     * @param driver database driver class
     */
    public ClockDatabase(String url, String username,
            String password, String driver) {

        if (url != null && url.length() > 0) {
            this.url = url;
        }
        if (username != null && username.length() > 0) {
            this.username = username;
        }
        if (password != null && password.length() > 0) {
            this.password = password;
        }
        if (driver != null && driver.length() > 0) {
            this.driver = driver;
        }
    }

    /**
     * Inserts a clocked period into the database.  Logic elsewhere should have
     * already deemed it necessary that we insert rather than update; i.e. we've
     * established that a user is clocking in.  Any exceptions raised are passed
     * on to the calling code.
     *
     * @param period details of the clocked in period entry
     * @throws SQLException
     */
    public void insertPeriod(DatabaseClockedPeriod period) throws SQLException {

        getDatabaseConnection();
        PreparedStatement ps = null;
        
        try {
            ps = (PreparedStatement) conn.prepareStatement(INSERT_PERIOD);
            
            ps.setInt(1, period.getBadgeNo());

            if (period.getEmployeeId() != 0) {
                ps.setInt(2, period.getEmployeeId());
            } else {
                ps.setNull(2, java.sql.Types.INTEGER);
            }

            if (period.getClockedIn() != null) {
                ps.setTimestamp(3, new Timestamp(
                        period.getClockedIn().getTime()));
            } else {
                ps.setNull(3, java.sql.Types.TIMESTAMP);
            }

            if (period.getClockedOut() != null) {
                ps.setTimestamp(4, new Timestamp(
                        period.getClockedOut().getTime()));
            } else {
                ps.setNull(4, java.sql.Types.TIMESTAMP);
            }

            ps.setBoolean(5, period.getSyntheticIn());
            ps.setBoolean(6, period.getSyntheticIn());

            ps.executeUpdate();
            conn.commit();

        } finally {
            safeClose(ps);
        }
    }

    /**
     * This method is responsible for updating an existing clocked period.  The
     * period is identified via a badge number and the time of clocking in.  All
     * other columns on the period are candidates for update via this method.
     * Any exceptions raised are passed on to the calling code.
     *
     * @param period the modified clocking period to be persisted
     * @throws SQLException
     */
    public void updatePeriod(DatabaseClockedPeriod period) throws SQLException {

        getDatabaseConnection();
        
        PreparedStatement ps = null;

        try {
            ps = (PreparedStatement) conn.prepareStatement(UPDATE_PERIOD);
            
            if (period.getEmployeeId() != 0) {
                ps.setInt(1, period.getEmployeeId());
            } else {
                ps.setNull(1, java.sql.Types.INTEGER);
            }

            ps.setTimestamp(2, new Timestamp(period.getClockedOut().getTime()));

            ps.setBoolean(3, period.getSyntheticIn());
            ps.setBoolean(4, period.getSyntheticIn());

            ps.setInt(5, period.getBadgeNo());

            ps.setTimestamp(6, new Timestamp(period.getClockedIn().getTime()));

            ps.executeUpdate();

            conn.commit();

        } finally {
            safeClose(ps);
        }
    }

    /**
     * This method fetches of all clocking periods for a given badge on a given
     * day.  The method is primarily used in identifying an "open" period that
     * is to be "clocked".  Any exceptions raised are passed on to the calling
     * code.
     *
     * @param badgeNo the number of the badge to be used in search
     * @param date the date to be used in the search
     * @return a list of candidate clocking periods for the criteria
     * @throws SQLException
     */
    public ArrayList<DatabaseClockedPeriod> getPeriodsForBadgeAndDate(
            int badgeNo, Date date) throws SQLException {

        getDatabaseConnection();

        // Calculate the start and end times of the specified date
        GregorianCalendar day = new GregorianCalendar();
        day.setTime(date);
        day.set(GregorianCalendar.HOUR_OF_DAY, 0);
        day.set(GregorianCalendar.MINUTE, 0);
        day.set(GregorianCalendar.SECOND, 0);
        day.set(GregorianCalendar.MILLISECOND, 0);

        Date start = day.getTime();
        day.add(GregorianCalendar.DATE, 1);
        Date stop = day.getTime();

        ArrayList<DatabaseClockedPeriod> result = new ArrayList<DatabaseClockedPeriod>();

        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = (PreparedStatement) conn.prepareStatement(
                    GET_CLOCKED_PERIODS_FOR_BADGE);
            
            ps.setInt(1, badgeNo);

            ps.setTimestamp(2, new Timestamp(start.getTime()));

            ps.setTimestamp(3, new Timestamp(stop.getTime()));

            rs = ps.executeQuery();

            while (rs.next()) {

                int employeeId = rs.getInt("employee_id");
                String employeeName = rs.getString("employee_name");

                Timestamp clockedIn = rs.getTimestamp("clocked_in");
                Timestamp clockedOut = rs.getTimestamp("clocked_out");

                boolean syntheticIn = rs.getBoolean("synthetic_in");
                boolean syntheticOut = rs.getBoolean("synthetic_out");

                DatabaseClockedPeriod clockedPeriod = new DatabaseClockedPeriod(badgeNo,
                        employeeId, employeeName, clockedIn, clockedOut,
                        syntheticIn, syntheticOut);

                result.add(clockedPeriod);
            }

            return result;

        } finally {
            safeClose(rs);
            safeClose(ps);
        }
    }

    /**
     * This method locates an "open" clocking periods.  The method's primary
     * purpose is in identifying periods to be synthetically closed where an
     * employee has forgotten to clock out.  Any exceptions raised are passed on
     * to the calling code.
     *
     * @return a list of open periods
     * @throws SQLException
     */
    public ArrayList<DatabaseClockedPeriod> getPeriodsForHousekeeping() throws SQLException {

        getDatabaseConnection();

        ArrayList<DatabaseClockedPeriod> result = new ArrayList<DatabaseClockedPeriod>();

        Statement s = null;
        ResultSet rs = null;

        try {
            s = (Statement) conn.createStatement();

            rs = s.executeQuery(GET_CLOCKED_PERIODS_FOR_HOUSEKEEPING);
            while (rs.next()) {
                
                int badgeId = rs.getInt("badge_id");
                
                int employeeId = rs.getInt("employee_id");
                String employeeName = rs.getString("employee_name");

                Timestamp clockedIn = rs.getTimestamp("clocked_in");
                Timestamp clockedOut = rs.getTimestamp("clocked_out");

                boolean syntheticIn = rs.getBoolean("synthetic_in");
                boolean syntheticOut = rs.getBoolean("synthetic_out");

                DatabaseClockedPeriod clockedPeriod = new DatabaseClockedPeriod(badgeId,
                        employeeId, employeeName, clockedIn, clockedOut,
                        syntheticIn, syntheticOut);

                result.add(clockedPeriod);
            }

            return result;

        } finally {
            safeClose(rs);
            safeClose(s);
        }
    }

    /**
     * This method simply gets details of all badges along with details of who
     * the badges have been allocated to.  Unallocated badges are also returned.
     * Any exceptions raised are passed on to the calling code.
     *
     * @return a list containing details of all known badges
     * @throws SQLException
     */
    public ArrayList<BadgeDetails> getBadgeDetails() throws SQLException {

        getDatabaseConnection();

        ArrayList<BadgeDetails> result = new ArrayList<BadgeDetails>();

        Statement s = null;
        ResultSet rs = null;

        try {
            s = (Statement) conn.createStatement();

            rs = s.executeQuery(GET_EMPLOYEES_AND_BADGES);
            while (rs.next()) {

                int badgeId = rs.getInt("badge_id");
                int employeeId = rs.getInt("employee_id");
                String employeeName = rs.getString("employee_name");

                BadgeDetails employeeDetails =
                        new BadgeDetails(badgeId, employeeId, employeeName);

                result.add(employeeDetails);
            }

            return result;

        } finally {
            safeClose(rs);
            safeClose(s);
        }
    }

    /**
     * This method simply gets details of a single badge along with details of
     * who the badge has been allocated to.  Even if unallocated, the badge will
     * still be returned.  Any exceptions raised are passed on to the calling
     * code.
     *
     * @return details of the request badge
     * @throws SQLException
     */
    public BadgeDetails getBadge(int badgeId) throws SQLException {

        getDatabaseConnection();

        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = (PreparedStatement) conn.prepareStatement(GET_EMPLOYEE_FOR_BADGE);
            ps.setInt(1, badgeId);
            rs = ps.executeQuery();

            if (rs.next()) {
                return new BadgeDetails(
                        rs.getInt("badge_id"),
                        rs.getInt("employee_id"),
                        rs.getString("employee_name"));
            } else {
                return null;
            }

        } finally {
            safeClose(rs);
            safeClose(ps);
        }
    }

    /**
     * Logs the clock transaction to the database; this could result in the user
     * clocking in or clocking out.  The transaction being passed in is devoid
     * of employee details and it's possible that the badge isn't even
     * recognised.  If the badge is totally unknown then it simply cannot be
     * logged.  If the badge is known but not allocated then we'll log it but
     * raise an event.
     *
     * @param transaction
     * @throws SQLException
     */
    public void clockInOrOut(DatabaseTransaction transaction) throws SQLException {

        int badgeNo = transaction.getBadgeNo();
        Date date = transaction.getClockDate();

        // Get details of the badge (and hopefully employee)
        BadgeDetails badgeDetails = getBadge(badgeNo);
        if (badgeDetails == null) {
            return;
        }
        
        // Search for the earliest "open" entry for the day

        ArrayList<DatabaseClockedPeriod> periods =
                getPeriodsForBadgeAndDate(badgeNo, date);
        
        for (DatabaseClockedPeriod period : periods) {

            // If we've already seen this datestamp then throw it away
            if (transaction.getClockDate().equals(period.getClockedIn()) ||
                    transaction.getClockDate().equals(period.getClockedOut())) {
                return;
            }

            // If we've found an open record then close it
            if (period.getClockedOut() == null) {
                period.setEmployeeId(badgeDetails.getEmployeeId());
                period.setEmployeeName(badgeDetails.getEmployeeName());
                period.setClockedOut(transaction.getClockDate());
                updatePeriod(period);

                transaction.setBadgeDetails(badgeDetails);
                transaction.setClockedInOrOut(ClockedInOrOut.CLOCKED_OUT);
                return;
            }
        }

        // If we've gotten to the end with nothing then create a new entry

        insertPeriod(new DatabaseClockedPeriod(
                transaction.getBadgeNo(),
                badgeDetails.getEmployeeId(),
                badgeDetails.getEmployeeName(),
                transaction.getClockDate(), null,
                false, false));
        
        transaction.setBadgeDetails(badgeDetails);
        transaction.setClockedInOrOut(ClockedInOrOut.CLOCKED_IN);
    }

    /**
     * This method will raise an event in the event log.  Such entries will
     * typically be raised when exceptional activity has been recorded; e.g. a
     * user is late clocking in, a user has failed to log out, etc.  Any
     * exceptions raised are passed on to the calling code.
     *
     * @param eventType
     * @param employeeId
     * @throws SQLException
     */
    public void raiseEvent(EventType eventType,
            int employeeId) throws SQLException {

        getDatabaseConnection();
        PreparedStatement ps = null;

        try {
            ps = (PreparedStatement) conn.prepareStatement(INSERT_EVENT);

            ps.setInt(1, employeeId);
            ps.setTimestamp(2, new Timestamp(new Date().getTime()));
            ps.setTimestamp(3, new Timestamp(new Date().getTime()));
            ps.setInt(4, eventType.ordinal());
            ps.executeUpdate();

        } finally {
            safeClose(ps);
        }
    }

    /**
     * This method is responsible for performing housekeeping on any "open"
     * clock periods; i.e. periods where a user has clocked in but has not yet
     * clocked out.  This method will typically run around midnight.  Any
     * exceptions raised are passed on to the calling code.
     *
     * @throws SQLException
     */
    public void performHousekeeping() throws SQLException {

        // Get details of "open" clock periods
        ArrayList<DatabaseClockedPeriod> periods = getPeriodsForHousekeeping();

        // Raise events for any users who've yet to clock out
        for (DatabaseClockedPeriod period : periods) {
            raiseEvent(EventType.NOT_CLOCKED_OUT, period.getEmployeeId());
        }

        // Close all of the records in a single query
        Statement s = null;
        try {
            s = (Statement) conn.createStatement();
            s.executeUpdate(CLOCKED_PERIOD_HOUSEKEEPING);
            conn.commit();

        } finally {
            safeClose(s);
        }
    }

    /**
     * This method will update the control record with the time of the computer
     * running the clock server application and the time last reported by the
     * clocking terminal.  Any exceptions raised are passed on to the calling
     * code.
     *
     * @param clockTime the date/time from the clocking terminal
     * @throws SQLException
     */
    public void recordClockTime(Date clockTime) throws SQLException {

        getDatabaseConnection();
        PreparedStatement ps = null;

        try {
            Date systemTime = new Date();

            ps = (PreparedStatement) conn.prepareStatement(RECORD_CLOCK_TIME);
            ps.setTimestamp(1, new Timestamp(clockTime.getTime()));
            ps.setTimestamp(2, new Timestamp(systemTime.getTime()));
            ps.executeUpdate();
            
            conn.commit();

        } finally {
            safeClose(ps);
        }
    }

    public boolean getTimeUpdateRequired() throws SQLException {
        return getControlBoolean("time_update_required");
    }

    public void clearTimeUpdateRequired() throws SQLException {
        setControlBoolean("time_update_required", false);
    }

    public boolean getDownloadRequired() throws SQLException {
        return getControlBoolean("download_required");
    }

    public void clearDownloadRequired() throws SQLException {
        setControlBoolean("download_required", false);
    }

    public boolean getHistoryUploadRequired() throws SQLException {
        return getControlBoolean("history_upload_required");
    }

    public void clearHistoryUploadRequired() throws SQLException {
        setControlBoolean("history_upload_required", false);
    }

    /**
     * Simple method to query a specific boolean column on the control table.
     * Any exceptions raised are passed on to the calling code.
     *
     * @param columnName the column name to query
     * @return the value of the boolean as per the database
     * @throws SQLException
     */
    private boolean getControlBoolean(String columnName)
            throws SQLException {

        getDatabaseConnection();

        Statement s = null;
        ResultSet rs = null;

        try {
            s = (Statement) conn.createStatement();
            rs = s.executeQuery(new Formatter().format(
                    GET_CONTROL_COLUMN,
                    columnName).toString());

            return rs.next() && rs.getBoolean(columnName);

        } finally {
            safeClose(rs);
            safeClose(s);
        }
    }

    /**
     * Simple method to query a specific boolean column on the control table.
     * Any exceptions raised are passed on to the calling code.
     *
     * @param columnName the column name to query
     * @return the value of the boolean as per the database
     * @throws SQLException
     */
    private void setControlBoolean(String columnName,
            boolean value) throws SQLException {

        getDatabaseConnection();

        Statement s = null;
        ResultSet rs = null;

        try {
            s = (Statement) conn.createStatement();
            s.executeUpdate(new Formatter().format(
                    SET_CONTROL_COLUMN,
                    columnName,
                    (value ? 1 : 0)).toString());

        } finally {
            safeClose(rs);
            safeClose(s);
        }
    }

    /**
     * Gets a working connection the database.  Note that if a good connection
     * already exists then we simply return the one.  If the connection has been
     * dropped then a new connection is established.  A boolean indicates
     * whether or not a valid connection has been established/re-used.
     *
     * Any exceptions are swallowed by this method and logged.
     *
     * @return a boolean indicating a good connection is available.
     * @throws SQLException
     */
    public boolean getDatabaseConnection() {

        // Attempt to execute a simple query using the existing connection

        if (conn != null) {
            Statement s = null;
            try {
                s = (Statement) conn.createStatement();
                s.execute(TEST_CONNECTION);
                disconnected = false;
                return true;

            } catch (SQLException e) {
                e.printStackTrace();
                Logger.getLogger(ClockDatabase.class.getName()).log(Level.SEVERE, null, e);

            } finally {
                safeClose(s);
            }
        }

        // Attempt to establish a new connection
        
        try {
            Class.forName(driver);
            conn = (Connection)java.sql.DriverManager.getConnection(url, username, password);
            conn.setAutoCommit(false);
            disconnected = false;

        } catch (ClassNotFoundException e) {
            Logger.getLogger(ClockDatabase.class.getName()).log(Level.SEVERE, null, e);
            disconnected = true;

        } catch (SQLException e) {
            if (!disconnected) {
                Logger.getLogger(ClockDatabase.class.getName()).log(Level.SEVERE, null, e);
            }
            disconnected = true;
        }

        return !disconnected;
    }

    /**
     * Simply closes the supplied result set and logs any errors reported.  Any
     * exception encountered are logged and swallowed.
     *
     * @param rs the result set to be closed
     */
    private void safeClose(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (Throwable ex) {
            Logger.getLogger(ClockDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
    }


    /**
     * Simply closes the supplied statement and logs any errors reported.  Any
     * exception encountered are logged and swallowed.
     *
     * @param s the statement to be closed
     */
    private void safeClose(Statement s) {
        try {
            if (s != null) {
                s.close();
            }
        } catch (Throwable ex) {
            Logger.getLogger(ClockDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}